package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.entities.Role;
import com.mbc.common.data.RoleDataList;
import com.mbc.common.param.CriteriaParams;

public class RoleDAOImpls extends DAO<Role> implements RoleDAO {

	public RoleDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "Roles";
	}

	@Override
	public void create(Role entity) throws SQLException {
		query = "insert into " + table_name + " ( RoleID, RoleName, Module, TopLevel, Description, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) values( ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getRoleID());
		pstmt.setString(i++, entity.getRoleName());
		pstmt.setString(i++, entity.getModule());
		pstmt.setInt(i++, entity.getTopLevel());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setDate(i++, new java.sql.Date(entity.getCreated_Date().getTime()));
		pstmt.setLong(i++, entity.getCreated_By());
		pstmt.setDate(i++, new java.sql.Date(entity.getUpdated_Date().getTime()));
		pstmt.setLong(i++, entity.getUpdated_By());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(Role entity) throws SQLException {
		query = "update " + table_name + " set  RoleName = ?, Module = ?, TopLevel = ?, Description = ?, CreatedDate = ?, CreatedBy = ?, UpdatedDate = ?, UpdatedBy = ?" +
				" where roleid = " + entity.getRoleID();
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setString(i++, entity.getRoleName());
		pstmt.setString(i++, entity.getModule());
		pstmt.setInt(i++, entity.getTopLevel());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setDate(i++, new java.sql.Date(entity.getCreated_Date().getTime()));
		pstmt.setLong(i++, entity.getCreated_By());
		pstmt.setDate(i++, new java.sql.Date(entity.getUpdated_Date().getTime()));
		pstmt.setLong(i++, entity.getUpdated_By());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where roleid = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public Role find(long key) throws SQLException {
		Role l_Roles = null;
		query = getSelectQuery() + " where roleid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Roles = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Roles;
	}

	@Override
	public ArrayList<Role> findAll() throws SQLException {
		ArrayList<Role> l_RolesList = new ArrayList<Role>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_RolesList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_RolesList;
	}

	@Override
	protected Role readRecord(ResultSet rs) throws SQLException {
		Role l_entity = new Role();
				l_entity.setRoleID(rs.getLong("RoleID"));
		l_entity.setRoleName(rs.getString("RoleName"));
		l_entity.setModule(rs.getString("Module"));
		l_entity.setTopLevel(rs.getInt("TopLevel"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setCreated_Date(rs.getDate("CreatedDate"));
		l_entity.setCreated_By(rs.getLong("CreatedBy"));
		l_entity.setUpdated_Date(rs.getDate("UpdatedDate"));
		l_entity.setUpdated_By(rs.getLong("UpdatedBy"));

		return l_entity;
	}

	public RoleDataList getList(CriteriaParams cri) throws SQLException{
		RoleDataList l_DataList = new RoleDataList();
		ArrayList<Role> l_entityList = new ArrayList<Role>();
		String l_cri = "";
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by RoleID desc) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			do {	
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(RoleID) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
    }
}
